package pl.mcnova.model.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;

import pl.mcnova.model.HibernateSession;
import pl.mcnova.model.entity.Device;

public class DeviceDAO {

	private SessionFactory sessionFactory;
	private Session session;

	public DeviceDAO() {

		sessionFactory = HibernateSession.getInstance();

	}

	public void save(Device device) {
		session = sessionFactory.openSession();
		Transaction transaction = null;

		try {
			transaction = session.beginTransaction();
			session.save(device);
			transaction.commit();
		} catch (Exception e) {
			if (transaction != null) {
				try {
					transaction.rollback();

				} catch (HibernateException he) {

				}
			}
		} finally {
			try {
				session.close();
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public void update(Device device) {
		session = sessionFactory.openSession();
		Transaction transaction = null;

		try {
			transaction = session.beginTransaction();
			session.update(device);
			transaction.commit();
		} catch (Exception e) {
			if (transaction != null) {
				try {
					transaction.rollback();

				} catch (HibernateException he) {

				}
			}
		} finally {
			try {
				session.close();
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public void delete(Device device) {
		session = sessionFactory.openSession();
		Transaction transaction = null;

		try {
			transaction = session.beginTransaction();
			session.delete(device);
			transaction.commit();
		} catch (Exception e) {
			if (transaction != null) {
				try {
					transaction.rollback();

				} catch (HibernateException he) {

				}
			}
		} finally {
			try {
				session.close();
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public void deleteAllOfDeviceNumber(Integer deviceNumber) {
		session = sessionFactory.openSession();
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session = sessionFactory.openSession();

			Query q = session.createQuery("delete from devices where deviceNumber = :deviceNumber");
			q.setInteger("deviceNumber", deviceNumber);
			q.list();

			transaction.commit();
		} catch (Exception e) {
			if (transaction != null) {
				try {
					transaction.rollback();

				} catch (HibernateException he) {

				}
			}
		} finally {
			try {
				session.close();

			} catch (HibernateException he) {
				throw he;
			}
		}

	}

	public List<Integer> getAllOfDeviceNumber(Integer deviceNumber) {
		session = sessionFactory.openSession();
		Transaction transaction = null;
		List<Integer> devices = null;

		try {
			transaction = session.beginTransaction();
			session = sessionFactory.openSession();

			Query q = session.createSQLQuery("select id from devices d where d.deviceNumber=" + deviceNumber);
			devices = q.list();

			transaction.commit();
		} catch (Exception e) {
			if (transaction != null) {
				try {
					transaction.rollback();

				} catch (HibernateException he) {

				}
			}
		} finally {
			try {
				session.close();
				return devices;
			} catch (HibernateException he) {
				throw he;
			}
		}

	}

	@SuppressWarnings("finally")
	public Device get(Integer deviceID) {
		session = sessionFactory.openSession();
		Device device = null;

		try {

			device = (Device) session.get(Device.class, deviceID);

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				return device;
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public List<Device> getAllfrom(Integer id) {
		session = sessionFactory.openSession();

		List<String> producers = null;
		List<String> models = null;
		List<Integer> Ids = null;
		List<Device> devices = null;
		List<Integer> maxNumber = null;
		Integer result = null;

		try {

			Query q1 = session.createSQLQuery("select id from devices where contractor_id = " + id.toString());
			Ids = q1.list();

			Query q = session.createSQLQuery("select producer from devices where contractor_id = " + id.toString());
			producers = q.list();

			Query q2 = session.createSQLQuery("select model from devices where contractor_id = " + id.toString());
			models = q2.list();

			Query max = session.createSQLQuery("SELECT max(deviceNumber) from devices order by deviceNumber");
			maxNumber = max.list();
			if (maxNumber == null)
				result = 1;
			else
				result = maxNumber.get(0) + 1;

			devices = new ArrayList<Device>();
			int i = 0;
			for (String s : producers) {
				Device d = new Device(models.get(i), s, result, null);
				d.setId(Ids.get(i));
				devices.add(d);
				i++;
			}

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				return devices;
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public Integer getAllOfNumber(Integer number) {
		session = sessionFactory.openSession();
		List<BigInteger> result = null;

		try {

			Query q = session.createSQLQuery("select count(*) from devices where deviceNumber=" + number);
			result = q.list();

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				if (result == null) return 0;
				return result.get(0).intValue();
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public List<Device> getAllGroupedByNumbers(String orderBy) {
		session = sessionFactory.openSession();
		List<Device> devices = null;

		try {

			Criteria criteria = session.createCriteria(Device.class);
			criteria.addOrder(Order.asc(orderBy));
			ProjectionList proList = Projections.projectionList();

			proList.add(Projections.property("id"), "id");
			proList.add(Projections.property("producer"), "producer");
			proList.add(Projections.property("model"), "model");
			proList.add(Projections.property("deviceNumber"), "deviceNumber");
			proList.add(Projections.groupProperty("deviceNumber"));
			devices = criteria.setProjection(proList).setResultTransformer(Transformers.aliasToBean(Device.class))
					.list();
			// devices =
			// criteria.setProjection(Projections.groupProperty("deviceNumber")).setResultTransformer(Transformers.aliasToBean(Device.class)).list();

			// devices =
			// session.createSQLQuery("select id, model,producer,contractor_id, deviceNumber from devices group by deviceNumber").setResultTransformer(Transformers.aliasToBean(Device.class)).list();

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				return devices;
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public List<String> getAllStringsGroupedByNumbers(String orderBy) {
		session = sessionFactory.openSession();
		List<Device> devices = null;
		List<String> result = new ArrayList<String>();

		try {

			Criteria criteria = session.createCriteria(Device.class);
			criteria.addOrder(Order.asc(orderBy));
			ProjectionList proList = Projections.projectionList();

			proList.add(Projections.property(orderBy), orderBy);
			proList.add(Projections.groupProperty("deviceNumber"));
			devices = criteria.list();

			if (orderBy.compareTo("model") == 0) {
				for (Device d : devices) {
					result.add(d.getModel());
				}
			} else if (orderBy.compareTo("producer") == 0) {
				for (Device d : devices) {
					result.add(d.getProducer());
				}
			}

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				return result;
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public Boolean deviceExists(String producer, String model) {
		session = sessionFactory.openSession();
		Boolean result = null;
		List<Device> devices = null;

		try {

			Criteria criteria = session.createCriteria(Device.class);
			criteria.add(Restrictions.like("producer", producer));
			criteria.add(Restrictions.like("model", model));
			devices = criteria.list();
			if (devices == null || devices.size() == 0)
				result = false;
			else
				result = true;

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				return result;
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public Integer getNewNumber() {
		session = sessionFactory.openSession();
		Integer result = null;
		List<Integer> maxNumber = null;

		try {

			Query max = session.createSQLQuery("SELECT max(deviceNumber) from devices order by deviceNumber");
			maxNumber = max.list();
			if (maxNumber == null || maxNumber.get(0) == null)
				result = 1;
			else
				result = maxNumber.get(0) + 1;

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				return result;
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public List<Device> getAll(String producer, String model) {
		session = sessionFactory.openSession();

		List<Device> devices = null;

		try {

			Criteria criteria = session.createCriteria(Device.class);
			criteria.add(Restrictions.like("producer", producer));
			criteria.add(Restrictions.like("model", model));
			devices = criteria.list();

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				return devices;
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public Device getFirst(Integer contractorId) {
		session = sessionFactory.openSession();

		try {

			Query q = session.createSQLQuery("select * from devices d where d.contractor_id = "
					+ contractorId.toString() + " limit 1");
			if (q.list() != null) return (Device) q.list().get(0);
			return null;

		} catch (Exception e) {

		} finally {
			try {
				session.close();
				return null;
			} catch (HibernateException he) {
				throw he;
			}
		}
	}

	public boolean checkContractorDevice(Integer contractorId, Integer deviceNumber) {
		session = sessionFactory.openSession();

		List list = null;

		try {

			Query q = session.createSQLQuery("select * from devices d where d.contractor_id = "
					+ contractorId.toString() + " and d.deviceNumber = " + deviceNumber.toString());
			list = q.list();

		} catch (Exception e) {

		} finally {
			try {
				session.close();

				if (list != null && list.size() > 0) return true;
				return false;

			} catch (HibernateException he) {
				throw he;
			}
		}
	}
}
